package jdbcHomework;

import java.sql.*;
import java.util.Date;

public class UserDao implements ConnactionInterFace {
    public static void main(String[] args) {
        UserDao ud = new UserDao();
        String sql1 = " insert into User(name,pwd,email,birthday) values('张三','888888','zhangsan@126.com','1986-10-11') ";
        String sql2 = " insert into User(name,pwd,email,birthday) values('李四','999999','lisi@126.com','1988-10-23') ";
        String sql3 = " insert into User(name,pwd,email,birthday) values('王五','777777','wangwu@126.com','1990-9-11') ";
        System.out.println(ud.executeInsert(sql1));
        System.out.println(ud.executeInsert(sql2));
        System.out.println(ud.executeInsert(sql3));
        System.out.println(ud.executeUpdateBirth());
        System.out.println(ud.executeDelete());
    }

    public static int executeInsert(String sql){
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection conn = null;
        Statement stmt = null;
        int r = 0;
        try {
            conn = DriverManager.getConnection(url,user,password);
            stmt = conn.createStatement();
            r = stmt.executeUpdate(sql);
            if(r>0){
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if(stmt==null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    stmt = null;
                }
            }
            if(conn==null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    conn = null;
                }
            }
        }
        return r;
    }

    public static int executeUpdateBirth(){
        String sql = " update User set birthday= now() where name = '张三' ";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection conn = null;
        Statement stmt = null;
        int r = 0;
        try {
            conn = DriverManager.getConnection(url,user,password);
            stmt = conn.createStatement();
            r = stmt.executeUpdate(sql);
            if(r>0){
                System.out.println("修改成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if(stmt==null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    stmt = null;
                }
            }
            if(conn==null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    conn = null;
                }
            }
        }
        return r;
    }

    public static int executeDelete(){
        String sql = " delete from User where name = '李四' ";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection conn = null;
        Statement stmt = null;
        int r = 0;
        try {
            conn = DriverManager.getConnection(url,user,password);
            stmt = conn.createStatement();
            r = stmt.executeUpdate(sql);
            if(r>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if(stmt==null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    stmt = null;
                }
            }
            if(conn==null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    conn = null;
                }
            }
        }
        return r;
    }
}
